package com.app.car.util;

import android.content.Context;

import com.app.car.bean.RfidListBean;
import com.app.car.bean.RfidListData;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import my.LogUtil;
import my.TimeUtils;

public class ExcelUtil {

    private static WritableFont arial14font = null;

    private static WritableCellFormat arial14format = null;
    private static WritableFont arial10font = null;
    private static WritableCellFormat arial10format = null;
    private static WritableFont arial12font = null;
    private static WritableCellFormat arial12format = null;
    private final static String UTF8_ENCODING = "UTF-8";


    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    private static void format() {
        try {
            arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            arial14font.setColour(Colour.LIGHT_BLUE);
            arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(jxl.format.Alignment.CENTRE);
            arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial14format.setBackground(Colour.VERY_LIGHT_YELLOW);

            arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial10format.setBackground(Colour.GRAY_25);

            arial12font = new WritableFont(WritableFont.ARIAL, 10);
            arial12format = new WritableCellFormat(arial12font);
            //对齐格式
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            //设置边框
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

        } catch (WriteException e) {
            e.printStackTrace();
        }
    }


    /**
     * 初始化Excel表格
     *
     * @param filePath  存放excel文件的路径（path/demo.xls）
     * @param sheetName Excel表格的表名
     * @param colName   excel中包含的列名（可以有多个）
     */
    public static void initExcel(String filePath, String sheetName, String[] colName) {
        format();
        WritableWorkbook workbook = null;
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            } else {
                return;
            }
            workbook = Workbook.createWorkbook(file);
            //设置表格的名字
            WritableSheet sheet = workbook.createSheet(sheetName, 0);
            //创建标题栏
            sheet.addCell((WritableCell) new Label(0, 0, filePath, arial14format));
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 0, colName[col], arial10format));
            }
            //设置行高
            sheet.setRowView(0, 340);
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
            LogUtil.d("导出失败。。。。。。。。。。initExcel123");
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 将制定类型的List写入Excel中
     *
     * @param fileName
     * @param mContext
     * @param <T>
     */
    @SuppressWarnings("unchecked")
    public static <T> void writeObjListToExcel(RfidListBean listBean, String fileName, Context mContext) {
        if (listBean != null && listBean.getCheckRfidList().size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);

                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName), workbook);
                WritableSheet sheet = writebook.getSheet(0);

                List<RfidListData> bookRfidList = listBean.getBookRfidList();
                int max = listBean.getMaxSize();
                for (int j = 0; j < max; j++) {
                    List<String> list = new ArrayList<>();
//                    LogUtil.d("导出for=j="+j);
                    if(j<bookRfidList.size()){
                        RfidListData demoBean = bookRfidList.get(j);
                        list.add(demoBean.getWare());
                        list.add(demoBean.getRfid());
                        list.add(demoBean.getType());
                        list.add(demoBean.getGid());
                        list.add(demoBean.getName());
                        list.add(demoBean.getSpecs());
                    }else {
                        list.add("");
                        list.add("");
                        list.add("");
                        list.add("");
                        list.add("");
                        list.add("");
                    }

                    if(j<listBean.getCheckRfidList().size()){
                        RfidListData demoBean = listBean.getCheckRfidList().get(j);
                        list.add(demoBean.getRfid());
                    }else {
                        list.add("");
                    }

                    if(j<listBean.getPanYingList().size()){
                        RfidListData demoBean = listBean.getPanYingList().get(j);
                        list.add(demoBean.getRfid());
                    }else {
                        list.add("");
                    }

                    if(j<listBean.getPanKuiList().size()){
                        RfidListData demoBean = listBean.getPanKuiList().get(j);
                        list.add(demoBean.getRfid());
                    }else {
                        list.add("");
                    }

                    list.add(TimeUtils.getTimeLongToStrByFormat(listBean.getTime(), "yyyy"));
                    list.add(TimeUtils.getTimeLongToStrByFormat(listBean.getTime(), "MM"));


                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j + 1, list.get(i), arial12format));
                        if (list.get(i).length() <= 4) {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 8);
                        } else {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 5);
                        }
                    }
                    //设置行高
                    sheet.setRowView(j + 1, 350);
                }

                writebook.write();
                workbook.close();
                LogUtil.e("导出Excel成功");
            } catch (Exception e) {
                e.printStackTrace();
                LogUtil.d("导出失败。。。。。。。。。。initExcel456");
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }





    public static ArrayList<RfidListData> parseFromExcel(String path) {
        Workbook book = null;//Excel工作簿对象
        File tempFile = new File(path);//临时文件，第二个参数为文件名字，可随便取

        ArrayList<String> wareList = new ArrayList<>();
        ArrayList<String> rfidList = new ArrayList<>();
        ArrayList<String> typeList = new ArrayList<>();
        ArrayList<String> gidList = new ArrayList<>();
        ArrayList<String> nameList = new ArrayList<>();
        ArrayList<String> specsList = new ArrayList<>();
        try {

            book = Workbook.getWorkbook(tempFile);//用读取到的表格文件来实例化工作簿对象（符合常理，我们所希望操作的就是Excel工作簿文件）
            Sheet[] sheets = book.getSheets(); //得到所有的工作表
            for (int m = 0; m < sheets.length; m++) {
                Sheet sheet = book.getSheet(m);
                int Rows = sheet.getRows();//得到当前工作表的行数
                int Cols = sheet.getColumns(); //得到当前工作表的列数
                for (int i = 0; i < Cols; i++) {  // 注意：这里是按列读取的！！！
                    for (int j = 0; j < Rows; j++) {
                        String content=sheet.getCell(i, j).getContents();//结果是String类型的，根据具体需求进行类型转换
                        if(content.contains("仓库") || content.contains("类别")
                                || content.contains("箱码") || content.contains("编码")
                                || content.contains("名称") || content.contains("规格") ) continue;

                        switch (i){
                            case 0:
                                wareList.add(content);
                                break;
                            case 1:
                                typeList.add(content);
                                break;
                            case 2:
                                rfidList.add(content);
                                break;
                            case 3:
                                gidList.add(content);
                                break;
                            case 4:
                                nameList.add(content);
                                break;
                            case 5:
                                specsList.add(content);
                                break;
                        }

                    }
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }

        ArrayList<RfidListData> rfidListData = new ArrayList<>();
        for (int i = 0 ;i<rfidList.size();i++){
            RfidListData data = new RfidListData();
            data.setWare(wareList.get(i));
            data.setRfid(rfidList.get(i));
            data.setType(typeList.get(i));
            data.setGid(gidList.get(i));
            data.setName(nameList.get(i));
            data.setSpecs(specsList.get(i));
            rfidListData.add(data);
        }
        return rfidListData;

    }
//    原文链接：https://www.qb5200.com/article/310559.html


}
